-- 石家庄
WITH sjz_operate_detail AS
(
    SELECT  waybill_no
           ,scan_site_code
           ,scan_site
           ,scan_time
           ,dt
           ,scan_type
    FROM
    (
        SELECT  *
               ,row_number() over (partition by dt,waybill_no ORDER BY scan_time) AS rn
        FROM
        (
            SELECT  waybill_no
                   ,scan_site_code
                   ,scan_site
                   ,scan_time
                   ,dt
                   ,scan_type
            FROM jms_dwd.dwd_tab_barscan_centerarrival_base_dt
            WHERE dt >= date_sub('{{ execution_date | cst_ds }}', 6)
            AND dt <= '{{ execution_date | cst_ds }}'
            AND scan_site_code = '731101' -- 石家庄
        ) tmp
    ) tmp2
    WHERE rn = 1
), sjz_un_sign_wide_waybill AS
(
    SELECT  a.waybill_no
           ,a.first_center_network_code
           ,a.pick_network_code
           ,b.name as pick_network_name
    FROM jms_dwd.dwd_wide_unsign_summary_waybill_dt a
        left join
            jms_dim.dim_network_whole_massage b
                on a.pick_network_code = b.code
    WHERE dt >= date_sub('{{ execution_date | cst_ds }}', 15)
    AND dt <= '{{ execution_date | cst_ds }}'
    AND first_center_network_code = '731101'
    AND pick_network_code in ('7317192', '7317202')
),
-- 保定
bd_operate_detail AS
(
    SELECT  waybill_no
           ,scan_site_code
           ,scan_site
           ,scan_time
           ,dt
    FROM
    (
        SELECT  *
               ,row_number() over (partition by dt,waybill_no ORDER BY scan_time) AS rn
        FROM
        (
            SELECT  waybill_no
                   ,scan_site_code
                   ,scan_site
                   ,scan_time
                   ,dt
            FROM jms_dwd.dwd_tab_barscan_centerarrival_base_dt
            WHERE dt >= date_sub('{{ execution_date | cst_ds }}', 6)
            AND dt <= '{{ execution_date | cst_ds }}'
            AND scan_site_code = '731201' 
            UNION ALL
            SELECT  waybill_no
                   ,scan_site_code
                   ,scan_site
                   ,scan_time
                   ,dt
            FROM jms_dwd.dwd_tab_barscan_centersend_base_dt
            WHERE dt >= date_sub('{{ execution_date | cst_ds }}', 6)
            AND dt <= '{{ execution_date | cst_ds }}'
            AND scan_site_code = '731201' 
            UNION ALL
            SELECT  waybill_no
                   ,scan_site_code
                   ,scan_site
                   ,scan_time
                   ,dt
            FROM jms_dwd.dwd_tab_barscan_bagging_base_dt
            WHERE dt >= date_sub('{{ execution_date | cst_ds }}', 6)
            AND dt <= '{{ execution_date | cst_ds }}'
            AND scan_site_code = '731201'
        ) tmp
    ) tmp2
    WHERE rn = 1 
), bd_un_sign_wide_waybill AS
(
    SELECT  a.waybill_no
           ,a.first_center_network_code
           ,a.pick_network_code
           ,b.name as pick_network_name
    FROM jms_dwd.dwd_wide_unsign_summary_waybill_dt a
            left join
            jms_dim.dim_network_whole_massage b
                on a.pick_network_code = b.code
    WHERE dt >= date_sub('{{ execution_date | cst_ds }}', 15)
    AND dt <= '{{ execution_date | cst_ds }}'
    AND first_center_network_code = '731201'
    AND pick_network_code in( '7010784', '7010788', '7010789', '7010785', '7010786', '7010787' )   -- 保定
)
insert overwrite table jms_dm.dm_first_center_arrival_statistics_dt partition (dt)
SELECT  t1.scan_site_code         -- 扫描站点code
       ,t1.scan_site              -- 扫描站点名称
       ,t1.dt    as scan_date     -- 扫描日期
       ,t2.pick_network_code      -- 揽收网点code
       ,t2.pick_network_name      -- 揽收网点名称
       ,COUNT(1)                  -- 扫描数量
       ,t1.dt                     -- 扫描日期
FROM sjz_operate_detail t1
JOIN sjz_un_sign_wide_waybill t2
ON t1.waybill_no = t2.waybill_no
GROUP BY  t1.scan_site_code
         ,t1.scan_site
         ,t2.pick_network_code
         ,t2.pick_network_name
         ,t1.dt

union all

SELECT  t1.scan_site_code         -- 扫描站点code
       ,t1.scan_site              -- 扫描站点名称
       ,t1.dt    as scan_date     -- 扫描日期
       ,t2.pick_network_code      -- 揽收网点code
       ,t2.pick_network_name      -- 揽收网点名称
       ,COUNT(1)                  -- 扫描数量
       ,t1.dt                     -- 扫描日期
FROM bd_operate_detail t1
JOIN bd_un_sign_wide_waybill t2
ON t1.waybill_no = t2.waybill_no
GROUP BY  t1.scan_site_code
         ,t1.scan_site
         ,t2.pick_network_code
         ,t2.pick_network_name
         ,t1.dt
distribute by 1
;


